import datetime
from auto_interface.testting import oracle_conn

class Deal_data():
    @classmethod
    def table_deal_with(cls, ioareaflag, imep_code, ve_licenseno, container_no, other_wt, other_container_time, other_ve_time):
        if other_wt is not '':
            table_name = other_container_time.split('、')[0]
            table_wt_field = 'CNTR_VGM'
            table_wt_value = other_wt.split('、')[2]
            table_field = 'TRUCK_NO'
            table_value = ve_licenseno

            table_id_field = 'id'

            table_time_value = (datetime.datetime.now() - datetime.timedelta(hours=float(other_container_time.split('、')[1]))).strftime('%Y-%m-%d %H:%M:%S')
            table_time_field = 'OPPC_TIME'

            table_area_field = 'TERMINAL'
            table_area_value = " like '%洋%'"
            table_up_area_value = "盛东(洋1)"

            table_ie_field = 'IEFG'
            table_ie_value = ioareaflag
            table_ie_up_value = ioareaflag

            if table_name == "CUSTOMS_SG_CNTR_ACTIVE":
                if imep_code not in ('2248', '2249'):
                    table_area_value = "like '%外%'"
                    table_up_area_value = "盛东(外1)"
                if other_wt.split('、')[1] == 'con':
                    table_field = 'CNTR_NO'
                    table_value = container_no.split('，')[0]
                    is_live = oracle_conn.Oracle_conn.select("select %s from %s where %s = '%s' and %s %s " % (
                        table_id_field, table_name, table_field, table_value, table_area_field, table_area_value))
                    if is_live is '':
                        id_now = oracle_conn.Oracle_conn.select(
                            "select %s from %s order by %s desc" % (table_id_field, table_name, table_id_field))
                        sql = Deal_data.customs_sg_cntr_active_insert(id_now=id_now,
                                                                      ioareaflag=ioareaflag,
                                                                      table_time_value=table_time_value,
                                                                      table_area_value=table_up_area_value,
                                                                      container_value=container_no,
                                                                      wt_value=table_wt_value)
                    else:
                        sql = "update %s set %s = to_date('%s','yyyy-MM-dd hh24:mi:ss') where %s = %s" % (
                            table_name, table_time_field, table_time_value, table_id_field, is_live)
                    oracle_conn.Oracle_conn.update(sql)
                is_live = oracle_conn.Oracle_conn.select("select %s from %s where %s = '%s' and %s %s " % (
                    table_id_field, table_name, table_field, table_value, table_area_field, table_area_value))
                if is_live is '':
                    id_now = oracle_conn.Oracle_conn.select(
                        "select %s from %s order by %s desc" % (table_id_field, table_name, table_id_field))
                    sql = Deal_data.customs_sg_cntr_active_insert(id_now=id_now,
                                                                  ioareaflag=ioareaflag,
                                                                  table_time_value=table_time_value,
                                                                  table_area_value=table_up_area_value,
                                                                  ve_licenseno_value=ve_licenseno,
                                                                  wt_value=table_wt_value)
                else:
                    sql = "update %s set %s = to_date('%s','yyyy-MM-dd hh24:mi:ss') where %s = %s" % (
                        table_name, table_time_field, table_time_value, table_id_field, is_live)
                oracle_conn.Oracle_conn.update(sql)

            elif table_name == "TB_CAR_LOG":
                """一号卡表  出区道口字段 并未根据 进出方向取"""
                table_id_field = 'SEQ_NUMBER'
                table_area_field = 'EX_STACK_ID'
                table_area_value = " like '48%'"
                table_up_area_value = '480001'
                table_field = 'CONTA_NO1'
                table_time_field = 'EX_TIME'
                if imep_code not in ('2248', '2249'):
                    table_area_value = " not like '48%'"
                    table_up_area_value = "999999"
                table_field = 'CAR_NO'
                table_wt_field = 'IM_WT'
                if other_wt.split('、')[1] == 'con':
                    table_field = 'CONTA_NO1'
                    table_value = container_no.split('，')[0]
            sql = "update %s set %s = %s where %s = '%s'" % (table_name, table_wt_field, table_wt_value, table_field, table_value)

            if table_name == "RE_LIST":
                table_field = 'VE_LICENSENO'
                table_wt_field = 'WT_GROSS'
                if other_wt.split('、')[1] == 'con':
                    table_field = 'CONTAINER_NO'
                    table_value = container_no.split('，')[0]
                sql = "update %s set %s = %s where %s = '%s'" % (table_name, table_wt_field, table_wt_value, table_field, table_value)
            oracle_conn.Oracle_conn.update(sql)

            is_live = oracle_conn.Oracle_conn.select("select %s from %s where %s = '%s' and %s %s " % (
            table_id_field, table_name, table_field, table_value, table_area_field, table_area_value))
            if is_live is '':
                id_now = oracle_conn.Oracle_conn.select(
                    "select %s from %s order by %s desc" % (table_id_field, table_name, table_id_field))
                if table_name == "CUSTOMS_SG_CNTR_ACTIVE":
                    sql = Deal_data.customs_sg_cntr_active_insert(id_now=id_now,
                                                                  ioareaflag=ioareaflag,
                                                                  table_time_value=table_time_value,
                                                                  table_area_value=table_up_area_value,
                                                                  container_value=container_no)
                elif table_name == "TB_CAR_LOG":
                    sql = Deal_data.tb_car_log_insert(id_now=id_now,
                                                      ioareaflag=ioareaflag,
                                                      table_time_value=table_time_value,
                                                      table_area_value=table_area_value,
                                                      container_value=ve_licenseno)
                oracle_conn.Oracle_conn.update(sql)
            else:
                sql = "update %s set %s = to_date('%s','yyyy-MM-dd hh24:mi:ss') where %s = %s" % (
                    table_name, table_time_field, table_time_value, table_id_field, is_live)
                oracle_conn.Oracle_conn.update(sql)

            if table_name == "RE_LIST":
                table_up_field = 'GATETIME'
                table_field = 'CONTAINER_NO'

                oracle_conn.Oracle_conn.update(sql)

        if other_container_time is not '':
            table_name = other_container_time.split('、')[0]

            table_id_field = 'id'

            table_time_value = (datetime.datetime.now() - datetime.timedelta(hours=float(other_container_time.split('、')[1]))).strftime('%Y-%m-%d %H:%M:%S')
            table_time_field = 'OPPC_TIME'

            table_field = 'CNTR_NO'
            table_value = container_no.split('，')[0]

            table_area_field = 'TERMINAL'
            table_area_value = " like '%洋%'"
            table_up_area_value = "盛东(洋1)"

            table_ie_field = 'IEFG'
            table_ie_value = ioareaflag
            table_ie_up_value = ioareaflag
            if table_name == "CUSTOMS_SG_CNTR_ACTIVE":
                if imep_code not in ('2248', '2249'):
                    table_area_value = "like '%外%'"
                    table_up_area_value = "盛东(外1)"

            if table_name == "TB_CAR_LOG":
                """一号卡表  出区道口字段 并未根据 进出方向取"""
                table_id_field = 'SEQ_NUMBER'
                table_area_field = 'EX_STACK_ID'
                table_area_value = " like '48%'"
                table_up_area_value = '480001'
                table_field = 'CONTA_NO1'
                table_time_field = 'EX_TIME'
                if imep_code not in ('2248', '2249'):
                    table_area_value = " not like '48%'"
                    table_up_area_value = "999999"

            is_live = oracle_conn.Oracle_conn.select("select %s from %s where %s = '%s' and %s %s " % (table_id_field, table_name, table_field, table_value, table_area_field, table_area_value))
            if is_live is '':
                id_now = oracle_conn.Oracle_conn.select("select %s from %s order by %s desc" % (table_id_field, table_name, table_id_field))
                if table_name == "CUSTOMS_SG_CNTR_ACTIVE":
                    sql = Deal_data.customs_sg_cntr_active_insert(id_now=id_now,
                                                                  ioareaflag=ioareaflag,
                                                                  table_time_value=table_time_value,
                                                                  table_area_value=table_up_area_value,
                                                                  container_value=container_no)
                elif table_name == "TB_CAR_LOG":
                    sql = Deal_data.tb_car_log_insert(id_now=id_now,
                                                      ioareaflag=ioareaflag,
                                                      table_time_value=table_time_value,
                                                      table_area_value=table_area_value,
                                                      container_value=ve_licenseno)
                oracle_conn.Oracle_conn.update(sql)
            else:
                sql = "update %s set %s = to_date('%s','yyyy-MM-dd hh24:mi:ss') where %s = %s" % (
                table_name, table_time_field, table_time_value, table_id_field, is_live)
                oracle_conn.Oracle_conn.update(sql)

            if table_name == "RE_LIST":
                table_up_field = 'GATETIME'
                table_field = 'CONTAINER_NO'

                oracle_conn.Oracle_conn.update(sql)

        if other_ve_time is not '':
            table_name = other_ve_time.split('、')[0]

            table_id_field = 'id'

            table_time_value = (datetime.datetime.now() - datetime.timedelta(hours=float(other_ve_time.split('、')[1]))).strftime('%Y-%m-%d %H:%M:%S')
            table_time_field = 'OPPC_TIME'

            table_field = 'TRUCK_NO'
            table_value = ve_licenseno

            table_area_field = 'TERMINAL'
            table_area_value = " like '%洋%'"
            table_up_area_value = "盛东(洋1)"

            table_ie_field = 'IEFG'
            table_ie_value = ioareaflag
            table_ie_up_value = ioareaflag
            if table_name == "TB_CAR_LOG":
                table_up_field = 'EX_TIME'
                table_field = 'CAR_NO'

            if table_name == "CUSTOMS_SG_CNTR_ACTIVE":
                if imep_code not in ('2248', '2249'):
                    table_area_value = "like '%外%'"
                    table_up_area_value = "盛东(外1)"

            if table_name == "TB_CAR_LOG":
                """一号卡表  出区道口字段 并未根据 进出方向取"""
                table_id_field = 'SEQ_NUMBER'
                table_area_field = 'EX_STACK_ID'
                table_area_value = " like '48%'"
                table_up_area_value = '480001'
                table_field = 'CAR_NO'
                table_time_field = 'EX_TIME'
                if imep_code not in ('2248', '2249'):
                    table_area_value = " not like '48%'"
                    table_up_area_value = "999999"

            is_live = oracle_conn.Oracle_conn.select("select %s from %s where %s = '%s' and %s %s " % (
            table_id_field, table_name, table_field, table_value, table_area_field, table_area_value))
            if is_live is '':
                id_now = oracle_conn.Oracle_conn.select(
                    "select %s from %s order by %s desc" % (table_id_field, table_name, table_id_field))
                if table_name == "CUSTOMS_SG_CNTR_ACTIVE":
                    sql = Deal_data.customs_sg_cntr_active_insert(id_now=id_now,
                                                                  ioareaflag=ioareaflag,
                                                                  table_time_value=table_time_value,
                                                                  table_area_value=table_up_area_value,
                                                                  ve_licenseno_value=ve_licenseno)
                elif table_name == "TB_CAR_LOG":
                    sql = Deal_data.tb_car_log_insert(id_now=id_now,
                                                      ioareaflag=ioareaflag,
                                                      table_time_value=table_time_value,
                                                      table_area_value=table_area_value,
                                                      ve_licenseno_value=ve_licenseno)
                oracle_conn.Oracle_conn.update(sql)
            else:
                sql = "update %s set %s = to_date('%s','yyyy-MM-dd hh24:mi:ss'), %s = '%s' where %s = %s" % (
                    table_name, table_time_field, table_time_value, table_field, table_value, table_id_field, is_live)

            if table_name == "RE_LIST":
                table_up_field = 'GATETIME'
                table_field = 'VE_LICENSENO'
                sql = "update %s set %s = to_date('%s','yyyy-MM-dd hh24:mi:ss'), %s = '%s' where id = 41124" % (table_name, table_up_field, table_time_value, table_field, table_value)
                oracle_conn.Oracle_conn.update(sql)

    @classmethod
    def customs_sg_cntr_active_insert(cls, id_now, ioareaflag, table_area_value, container_value='集装箱', wt_value='100',  ve_licenseno_value='车牌', table_time_value=(datetime.datetime.now() - datetime.timedelta(hours=12)).strftime('%Y-%m-%d %H:%M:%S')):
        sql = "INSERT INTO NEPZ.CUSTOMS_SG_CNTR_ACTIVE (ID, VSL_NME, VSL_NME_CN, VOY_REF, IEFG, CNTR_NO, CNTR_STATUS, CNTR_TYPE, CNTR_SIZE, CNTR_VGM, CNTP_OPMOD, OPPC_TIME, TRUCK_NO, BILL_NO, CNTR_DNGCD, CNTR_TEMP, CNTR_OPMOD, CNTR_OPPRC, TERMINAL, FLOW_YARD, PHONE_NO, REC_UPD_DT, UPLOAD_IND, MESSAGE_SENDER, CK_UPLOAD_IND, SRC_TIME, CAPXTIMESTAMP, COLUMN1) " \
              "VALUES(%s, 'SITC NINGBO', '海丰宁波', '2240N', '%s', '%s', '进口重箱', 'GP', '20', '%s', NULL, to_date('%s','yyyy-MM-dd hh24:mi:ss'), '%s', 'SNLCKECL101761', NULL, NULL, '提进口重箱', '场―车', '%s', NULL, NULL, '25-NOV-22 11.06.01.000000 PM', '2', 'mq_port', '0', '25-NOV-22 11.06.01.000000 PM', '25-NOV-22 11.06.01.914089 PM', NULL)" % \
              (int(id_now) + 1, ioareaflag, container_value, wt_value, table_time_value, table_area_value, ve_licenseno_value)
        return sql
    @classmethod
    def tb_car_log_insert(cls, id_now, ioareaflag, table_area_value, container_value='集装箱', wt_value='100',  ve_licenseno_value='车牌', table_time_value=(datetime.datetime.now() - datetime.timedelta(hours=12)).strftime('%Y-%m-%d %H:%M:%S')):
        sql = "INSERT INTO NEPZ.TB_CAR_LOG (SEQ_NUMBER, CAR_NO, IC_NO, EX_STACK_ID, EX_CHANNEL_ID, EX_TIME, EX_WT, EX_USER_NAME, IM_STACK_ID, IM_CHANNEL_ID, IM_TIME, IM_WT, IM_USER_NAME, CHECK_FLAG, CAR_TYPE, GPS_CHECK, GPS_TRACK, ESEAL_NO1, ESEAL_NO2, ESEAL_NO3, CONTA_NO1, CONTA_NO2, OPER_MODE, OPER_MODE2, TRANS_FLAG, DRIVER_FLAG, IE_FLAG, CAPXTIMESTAMP) " \
                          "VALUES(%s, '%s', NULL, %s, 'e01', to_date('%s','yyyy-MM-dd hh24:mi:ss'), %s, 0, %s, 'j08', to_date('%s','yyyy-MM-dd hh24:mi:ss'), %s, 0, 1, 0, NULL, NULL, NULL, NULL, NULL, '%s', 'DFSU6262418', '自动核销', '自动核销', NULL, NULL, '%s', '21-NOV-22 08.07.25.928638 PM')" % \
                          (int(id_now) + 1, ve_licenseno_value, table_area_value, table_time_value, wt_value, table_area_value, table_time_value, wt_value,  container_value, ioareaflag)
        return sql
if __name__ == "__main__":
    Deal_data()